import pymysql
from collections import defaultdict

from pandas import notnull

from export.db_config import db_config
import pandas as pd

connection = pymysql.connect(**db_config)
cursor = connection.cursor()

# 模拟从数据库获取数据
query = "SELECT * FROM statistics_clct_subject"
cursor.execute(query)
results = cursor.fetchall()

# 获取列名
columns = [desc[0] for desc in cursor.description]

# 转换为 DataFrame
df = pd.DataFrame(results, columns=columns)

# #根据正则提取字符串
# print(df['site_name'].drop_duplicates().str.extract(r'(\w+)'))

#对指定列进行筛选
# #过滤siteId为bnu的数据
# df = df[df['site_id'].str.lower() == 'bnu']
# print(df)
# print(len(df))

# print(df.filter(items=['site_id', 'subject_id']))
#筛选包含site的列（对列进行筛选）
# print(df.filter(like='site'))
#筛选以id结尾的列（对列进行筛选）
# print(df.filter(regex='id$'))
#筛选以s开头的列（对列进行筛选）
# print(df.filter(regex='^s'))
# # 分组，对分组进行过滤
# groupby = df.groupby(['site_id', 'subject_id'])
# print(groupby.filter(lambda x: (x['site_id'].iloc[0], x['subject_id'].iloc[0]) == ('BNU', '43kacf')))

# # 分组
# groupby = df.groupby(['site_id', 'subject_id'])
# #根据key获取值
# # group = groupby.get_group(('ECNU', 'g7vanw'))
# # print(group)
# #key是个元组
# for each_group_key, group in groupby:
#     print(type(each_group_key))
#     print(each_group_key[0], '   ',  group)

# print(df.groupby('site_id').count())
# groupby = df.groupby('site_id')
# for site_id, group in groupby:
#     print(type(site_id))
#     print(type(group))
#     print(len(group))


# #删除数据，inplace = True对原始数据进行删除
# print(df.info())
# # df.drop(1, inplace = True)
# drop = df.drop(1)
# print(drop.info())


# # 根据条件筛选数据，完成时间不为空的数据
# not_null = df[df['complete_clct_date'].notnull() & df['site_id'].eq('BNU')]
# print(not_null[['site_id', 'complete_clct_date']])

# #选择多行数据
# iloc = df.iloc[0:3]
# print(iloc['site_name'])

# #选择单行数据
# iloc = df.iloc[0]
# print(iloc)
# print(iloc['site_name'])
##取出site_name列第一行的值
# print(df.loc[0, 'site_name'])

# # 创建示例 DataFrame
# data = {
#     'name': ['Alice', 'Bob', 'Charlie'],
#     'minClctDate': ['1990-05-15', '1985-12-20', '2000-07-30'],
#     'value': [10, 20, 30]
# }
# df = pd.DataFrame(data)
#
# # 使用 iloc 取出第一行的 name 列的值
# name_first_row = df.iloc[0, 0]
# print(name_first_row)  # 输出: Alice
#
# # 使用 iloc 取出前两行的所有列
# first_two_rows = df.iloc[:2, :]
# print(first_two_rows)

# # 选择单列并进行去重
# print(df[ 'site_name'].drop_duplicates())

# # 选择多列并进行去重
# print(df[['site_id', 'site_name']].drop_duplicates())

# # 获取数据中的site_id,并去重
# duplicates = df['site_id'].drop_duplicates()
# site_ids = [site_id for site_id in duplicates]
# print(site_ids)

# 多维数组用iterrows()
# site_id = [row['site_id'] for index, row in df[['site_id', 'site_name']].drop_duplicates().iterrows()]

# # Series
# # 类似一维数组，由一组数据和相应的索引组成
# s = pd.Series([1, 'b', 'c', '你好啊'])
# print(s)


# # DataFrame 类似二维表格，由多组数据和相应的索引值组成
# df = pd.DataFrame({'name': ['张三', '李四', '王五'],
#                    'age': [18, 19, 20],
#                    'occupation': ['student', 'teacher', 'doctor']
#                    }
#                   )
# # 所有的数据
# print(df)
#
# # 查看前2行数据
# print(df.head(2))
#
# # 查看后2行数据
# print(df.tail(2))

# #显示 DataFrame 的列名、数据类型、非空值数量等基本信息
# print(df.info())

# # 显示 DataFrame 中数值列的描述性统计信息，如计数、平均值、标准差、最小值、最大值等。
# print(df.describe())
